package com.gxuwz.endrskyweather.service;

import android.app.Service;
import android.content.ContentValues;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.IBinder;
import android.util.Log;

import androidx.annotation.Nullable;
import com.gxuwz.endrskyweather.db.WeatherDBHelper;
import okhttp3.OkHttpClient;
import okhttp3.Request;
import okhttp3.Response;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class DataSyncService extends Service {
    private static final String TAG = "DataSyncService";
    private static final String XLSX_URL = "http://img.lundear.com/weather_code.xlsx";
    private ExecutorService executorService = Executors.newSingleThreadExecutor();
    private OkHttpClient client = new OkHttpClient();

    @Override
    public int onStartCommand(Intent intent, int flags, int startId) {
        executorService.execute(() -> {
            try {
                File xlsxFile = downloadXlsxFile();
                if (xlsxFile != null) {
                    parseAndStoreData(xlsxFile);
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                stopSelf();
            }
        });
        return START_STICKY;
    }

    private File downloadXlsxFile() throws Exception {
        Request request = new Request.Builder()
            .url(XLSX_URL)
            .build();
            
        Response response = client.newCall(request).execute();
        
        if (!response.isSuccessful()) {
            throw new Exception("HTTP " + response.code());
        }

        File outputFile = new File(getCacheDir(), "temp_data.xlsx");
        try (InputStream inputStream = response.body().byteStream();
             FileOutputStream outputStream = new FileOutputStream(outputFile)) {
            
            byte[] buffer = new byte[4096];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
            }
            
            return outputFile;
        }
    }

    private void parseAndStoreData(File xlsxFile) throws Exception {
        WeatherDBHelper dbHelper = new WeatherDBHelper(this);
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        
        boolean transactionStarted = false;
        Workbook workbook = null;
        try {
            workbook = new XSSFWorkbook(xlsxFile);
            Sheet sheet = workbook.getSheetAt(0);
            db.beginTransaction();
            transactionStarted = true;
//            // 打印表头信息
//            Row headerRow = sheet.getRow(0);
//            StringBuilder headerInfo = new StringBuilder("表头信息: ");
//            for (Cell cell : headerRow) {
//                headerInfo.append(getCellValue(cell)).append(" | ");
//            }
//            Log.d(TAG, headerInfo.toString());

            // 插入数据库前，先清空表
            db.delete(WeatherDBHelper.TABLE_AREA, null, null);
            // 遍历每行数据
            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue;
//                // 打印每行数据
//                StringBuilder rowData = new StringBuilder("行数据 " + row.getRowNum() + ": ");
//                for (int i = 0; i < 8; i++) {
//                    rowData.append(getCellValue(row.getCell(i))).append(" | ");
//                }
//                Log.d(TAG, rowData.toString());
                // 插入数据库
                ContentValues values = new ContentValues();
                values.put(WeatherDBHelper.COLUMN_AREA_CODE, getCellValue(row.getCell(0)));
                values.put(WeatherDBHelper.COLUMN_AREA_ID, getCellValue(row.getCell(1)));
                values.put(WeatherDBHelper.COLUMN_AREA_EN, getCellValue(row.getCell(2)));
                values.put(WeatherDBHelper.COLUMN_AREA_CN, getCellValue(row.getCell(3)));
                values.put(WeatherDBHelper.COLUMN_CITY_EN, getCellValue(row.getCell(4)));
                values.put(WeatherDBHelper.COLUMN_CITY_CN, getCellValue(row.getCell(5)));
                values.put(WeatherDBHelper.COLUMN_PROV_EN, getCellValue(row.getCell(6)));
                values.put(WeatherDBHelper.COLUMN_PROV_CN, getCellValue(row.getCell(7)));
                long id = db.insert(WeatherDBHelper.TABLE_AREA, null, values);
            }

//            // 读取数据库的前10行数据，用于测试是否成功插入数据
//            Cursor cursor = db.query(WeatherDBHelper.TABLE_AREA, null, null, null, null, null, null);
//            cursor.moveToFirst();
//            for (int i = 0; i < 10; i++) {
//                Log.d(TAG, "area_code: " + cursor.getString(cursor.getColumnIndex(WeatherDBHelper.COLUMN_AREA_CODE)) +
//                        ", area_id: " + cursor.getString(cursor.getColumnIndex(WeatherDBHelper.COLUMN_AREA_ID)) +
//                        ", area_cn: " + cursor.getString(cursor.getColumnIndex(WeatherDBHelper.COLUMN_AREA_CN)) +
//                        ", city_cn: " + cursor.getString(cursor.getColumnIndex(WeatherDBHelper.COLUMN_CITY_CN)) +
//                        ", prov_cn: " + cursor.getString(cursor.getColumnIndex(WeatherDBHelper.COLUMN_PROV_CN)));
//                cursor.moveToNext();
//            }
//            cursor.close();
            
            db.setTransactionSuccessful();
        } catch (Exception e) {
            Log.e(TAG, "Error parsing XLSX file", e);
            throw e;
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (transactionStarted) {
                    db.endTransaction();
                }
            } finally {
                db.close();
                xlsxFile.delete();
            }
        }
    }

    private String getCellValue(Cell cell) {
        if (cell == null) return "";
        // 无论是字符串还是数字，都转为字符串
        return String.valueOf(cell.getStringCellValue());
    }

    @Nullable
    @Override
    public IBinder onBind(Intent intent) {
        return null;
    }

    @Override
    public void onDestroy() {
        super.onDestroy();
        executorService.shutdown();
    }
}
